﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace SqlCeDriver
{
	public class SqlCEImportExportInterfaceImplementation : DbViewDriver.IDatabaseExportSource
	{
		public SqlCEImportExportInterfaceImplementation(SqlCEDataAccessor accessor)
		{
			this.dataAccessor = accessor;
		}
		/////////////////////////////////////////////////////////////////////
		//  DbViewDriver.IDatabaseExportSource interface
		public String ConnectionString
		{
		    get {return this.connectionString;}
		    set {connectionString = value;}
		}

		public DbView.FieldInfo[] GetSchema(String tableName)
		{
			if (Fields == null)
			{
				SqlTableSchema schema = Helper.MakeSchemaFor(tableName, this.dataAccessor, ConnectionString);
				Fields = new List<DbView.FieldInfo>();
				for (int idx = 0; idx < schema.Count; ++idx)
					Fields.Add(schema[idx]);
			}
			return Fields.ToArray();
		}

		public DataTable ReadData(String tableName, String [] fields, String filterClause, String orderClause)
		{
			String fieldList = "["+String.Join("],[", fields)+"]";
			String sql = String.Format("SELECT {1} from {0}", Helper.SafeTableName(tableName), fieldList);
			if (filterClause.Length > 0)
				sql += " where "+filterClause;
			if (orderClause.Length > 0)
				sql += " order by "+orderClause;
			DataTable dt = new Helper(this.dataAccessor).GenericQuery(ConnectionString, sql, tableName);
			return dt;
		}

		private String MakeSql(String tableName, String [] fields, String filterClause, String orderClause, bool forCount)
		{
			String fieldList = "["+String.Join("],[", fields)+"]";
			if (forCount) fieldList = "count(*)"; // counting not fetching fields
			String sql = String.Format("SELECT {1} from {0}", Helper.SafeTableName(tableName), fieldList);
			if (filterClause.Length > 0)
				sql += " where "+filterClause;
			if (orderClause.Length > 0 && !forCount)   // don't sort if counting. Query optimizer probably dumps this anyway
				sql += " order by "+orderClause;
			return sql;
		}

		public int ReadInit(String tableName, String [] fields, String filterClause, String orderClause)
		{
			String sql = MakeSql(tableName, fields, filterClause, orderClause, true);
			DataTable dt = this.dataAccessor.Query(ConnectionString, new String[] {sql}, tableName);
			int rowCount = Convert.ToInt32(dt.Rows[0][0]);
			// now set up the reader
			sql = MakeSql(tableName, fields, filterClause, orderClause, false);
			this.dataAccessor.ReadInit(ConnectionString, sql);
			return rowCount;
		}
		
		// read the data until exhausted
		public bool ReadNext(out object[] data)
		{
			return this.dataAccessor.ReadNext(out data);
		}
		// finish
		public void ReadClose()
		{
			this.dataAccessor.ReadClose();
		}

		public DataTable CreateTransferTable(String tableName)
		{
			DbView.FieldInfo[] fields = GetSchema(tableName);
			String fieldList="";
			foreach (DbView.FieldInfo field in fields)
			{
				if (fieldList.Length > 0) fieldList += ",";
				fieldList += field.SafeName;
			}
			String sql = String.Format("SELECT top (0) {1} from {0}", Helper.SafeTableName(tableName), fieldList);
			DataTable dt = new Helper(this.dataAccessor).GenericQuery(ConnectionString, sql, tableName);
			return dt;
		}
		
		// This will throw exceptions. Its up to the caller to deal with them as there
		// is not much you can do to recover here.
		public void PopulateTable(String tableName, DataTable data, bool isStaging)
		{
			String targetTableName = (isStaging) ? MakeStagingTableName(tableName) : tableName;
			DbView.FieldInfo[] fields = GetSchema(tableName);
			String autoIncField = "";
			foreach (DbView.FieldInfo fi in fields)
				if (fi.IsAuto) autoIncField = fi.Name;
			// do the SQL CE bit
			// need to establish whether there is an identitiy column
			// check existance of tgt
			this.dataAccessor.PopulateTable(ConnectionString, targetTableName, data, autoIncField, isStaging);
		}

		public void CreateStagingTable(String tableName, bool isStaging, bool create)
		{
			if (!isStaging) return; // not necessary
			if (create)
			{
				CreateStagingTable(tableName);
			}
			else
			{
				String stagingTableName = MakeStagingTableName(tableName);
				String tableScript = "drop table "+ stagingTableName; // should work.
				// execute it
				bool success = false;
				new Helper(this.dataAccessor).GenericExecute(ConnectionString, tableScript, out success);
			}
		}

		private String CreateStagingTable(String targetTableName)
		{
			// refactor into a new routine
			String stagingTableName = MakeStagingTableName(targetTableName);
			SqlTableSchema schema = Helper.MakeSchemaFor(targetTableName, this.dataAccessor, ConnectionString);
			SqlServerScripter scripter = new SqlServerScripter(targetTableName, schema);
			String makeTableScript = scripter.ScriptCreateTable(true);
			makeTableScript = ReplaceTargetWithStaging(makeTableScript, targetTableName, stagingTableName);
			// TODO: need to find whether it exists and make it if not.
			//makeTableScript = "if object_id('"+stagingTableName+"') is null "+ makeTableScript;
			// execute it
			bool success = false;
			new Helper(this.dataAccessor).GenericExecute(ConnectionString, makeTableScript, out success);
			return stagingTableName;
		}

		private String ReplaceTargetWithStaging(String script, String targetTableName, String stagingTableName)
		{
			// Deal with schema tweak in the script
			String seek = Helper.SafeTableName(targetTableName);
			String repl = Helper.SafeTableName(stagingTableName);

			script = script.Replace(seek, repl);
			// now attend the PK_FIELD
			// remember to deal with schema stuff
			seek = seek.Insert(1, "PK_");
			repl = repl.Insert(1, "PK_");
			script = script.Replace(seek, repl);
			return script;
		}

		// If staging update the from the staging table according to the given mode
		public void UpdateFromStaging(String tableName, String [] importedFields, DbViewDriver.CsvImportMode mode)
		{
			// At this point inject a few checks and set up a few others
			// This will hold items to be treated separately. These are fields with defaults so that 
			// null values will not be inserted instead of the default. This is better behaviour I think.
			DbView.FieldInfo [] specialList = MakeExcludedFieldList(ConnectionString, tableName, importedFields, Fields.ToArray(), mode);
			// filter the special fields from the mapped list.
			String [] groupFields = MakeGroupFieldList(importedFields, specialList);

			String stagingTableName = MakeStagingTableName(tableName);
			SqlTableSchema schema = Helper.MakeSchemaFor(tableName, this.dataAccessor, ConnectionString);
			SqlServerScripter scripter = new SqlServerScripter(tableName, schema);
			// ... and this will break in the case where the table name is a reserved word, but test for this later.
			SqlScriptingHelper helper = new SqlScriptingHelper(stagingTableName, schema);
			// set the filter to prevent non-mapped fields being added to the list (think updates mainly)
		//helper.SetFieldFilter(groupFields);
			// build useful SQL fragments from the configured object
			String pkeyCompare = helper.ScriptKeyCompare(tableName);
			String insertableFieldList = helper.ScriptAllButCalcFields();
			String updateableFieldList = helper.ScriptUpdate(tableName);
			// cater for the rare case where only PKeys are being updated and user has selected update mode.
			// switch to insert mode.
			if (updateableFieldList.Length == 0 && mode == DbViewDriver.CsvImportMode.Merge) mode = DbViewDriver.CsvImportMode.AppendOnly;
			// decide the template 
			String updateTemplate = (mode == DbViewDriver.CsvImportMode.Replace) ? SCRIPT_REPLACE :
									(mode == DbViewDriver.CsvImportMode.Merge) ? SCRIPT_MERGE :
									(mode == DbViewDriver.CsvImportMode.UpdateOnly) ? SCRIPT_UPDATE_ONLY :
									(mode == DbViewDriver.CsvImportMode.AppendOnly) ? SCRIPT_APPEND_ONLY : "";

			// {0} is target table {1} is staging {2} is a list of fields to update 
			// {3} is a pkey comparison {4} is an update statement
			String script = (mode == DbViewDriver.CsvImportMode.Replace) ? String.Format(updateTemplate, Helper.SafeTableName(tableName), Helper.SafeTableName(stagingTableName), insertableFieldList) :
							(mode == DbViewDriver.CsvImportMode.Merge) ? String.Format(updateTemplate, Helper.SafeTableName(tableName), Helper.SafeTableName(stagingTableName), insertableFieldList, pkeyCompare, updateableFieldList) :
							(mode == DbViewDriver.CsvImportMode.UpdateOnly) ? String.Format(updateTemplate, Helper.SafeTableName(tableName), Helper.SafeTableName(stagingTableName), insertableFieldList, pkeyCompare, updateableFieldList) :
							(mode == DbViewDriver.CsvImportMode.AppendOnly) ? String.Format(updateTemplate, Helper.SafeTableName(tableName), Helper.SafeTableName(stagingTableName), insertableFieldList, pkeyCompare) : "";
			// if auto-inc key wrap the script in the special code. Not if update only because no new rows are added.
			if (schema.IdentityField != null && mode != DbViewDriver.CsvImportMode.UpdateOnly)
			{
				//// something better than this
				script = String.Format(@"
set IDENTITY_INSERT [{0}] ON;
{1}
set IDENTITY_INSERT [{0}] OFF;
",tableName, script);
			}
			// do it.
			bool success = false;
			// here need to check whether to perform an update or not
			if (mode == DbViewDriver.CsvImportMode.Merge || mode == DbViewDriver.CsvImportMode.UpdateOnly)
			{
				String[] pkeys;
				String[] updateFields;
				MakeUpdateTableParams(schema, importedFields, out pkeys, out updateFields);
				this.dataAccessor.UpdateTable(this.connectionString, tableName, stagingTableName, pkeys, updateFields);
			}

			new Helper(this.dataAccessor).GenericExecute(ConnectionString, script, out success);
			if (schema.IdentityField != null && mode != DbViewDriver.CsvImportMode.UpdateOnly)
			{
				new Helper(this.dataAccessor).ResetAutoIdentity(this.connectionString, tableName);
			}
		}

		// gets the pkeys and extracts non-pkey fields from the import field list.
		private void MakeUpdateTableParams(SqlTableSchema schema, string[] importedFields, out string[] pkeys, out string[] updateFields)
		{
			List<String> pkeyList = new List<string>();
			foreach (DbView.FieldInfo fi in schema.PKeyFields)
				pkeyList.Add(fi.Name);
			List<String> updateList = new List<string>();
			foreach (String field in importedFields)
			{
				if (pkeyList.IndexOf(field) == -1)
					updateList.Add(field);
			}
			pkeys = pkeyList.ToArray();
			updateFields = updateList.ToArray();
		}

		public int CountRows(String tableName, String filterClause)
		{
			String sql = "Select count(*) from " + Helper.SafeTableName(tableName);
			if (filterClause.Length > 0) sql += " where "+filterClause;
			DataTable dt = new Helper(this.dataAccessor).GenericQuery(ConnectionString, sql, "result");
			int rowCount = (int)dt.Rows[0][0];
			return rowCount;
		}

		private string[] MakeGroupFieldList(string[] importedFields, DbView.FieldInfo[] specialList)
		{
			List<String> fieldList = new List<String>();
			// removed the SpecialList fields from teh importedFields.
			foreach (String field in importedFields)
			{
				bool found = false;
				foreach (DbView.FieldInfo fi in specialList)
				{
					if (fi.Name == field)
						found = true;
				}
				if (!found)
					fieldList.Add(field);
			}
			return fieldList.ToArray();
		}

		// This to find fields that will cause problems if imported directly from staging
		// Inherited from SQL Server. Best to remove if possible. 
		private DbView.FieldInfo [] MakeExcludedFieldList(String connectionString, String tableName, String[] importedFields, DbView.FieldInfo [] schema, DbViewDriver.CsvImportMode mode)
		{
			List<DbView.FieldInfo> fieldList = new List<DbView.FieldInfo>();
			// include Non-null fields with and without defaults. Actually we need to 
			foreach (String field in importedFields)
			{
				foreach (DbView.FieldInfo fi in schema)
				{
					if (!fi.IsPKey)
						if (fi.Name == field && !fi.AllowsNulls)
						{
							// a. find whether there are any blank values in staging
							int nullCount = CountNullsInStaging(connectionString, tableName, field);
							// if N then do not add
							// if Y and there is a default then add to special
							// if Y and there is no default then do not add and let the operation abort.
							// if Y and mode is append only abort
							if (nullCount > 0)
							{
								if (mode == DbViewDriver.CsvImportMode.AppendOnly)
									throw new Exception(String.Format("Unable to continue in append only mode because import data for field [{0}] contains empty values which [{0}] cannot have.", fi.Name));
							}
						}
				}
			}
			return fieldList.ToArray();
		}

		private int CountNullsInStaging(string connectionString, string tableName, string field)
		{
			String sql = String.Format("select count({1}) as C from {0}", Helper.SafeTableName(MakeStagingTableName(tableName)), field);
			DataTable dt = new Helper(this.dataAccessor).GenericQuery(connectionString, sql, "result");
			return (int)dt.Rows[0][0];
		}
		private string MakeStagingTableName(string tableName)
		{
			// Ultimate goal is to refactor to use a real temp table
			// ## is a persistent temp table.
			// also remove schemaness
			// problems with bulk copy and global temp table. Use decorated name for now
			// return String.Format("##{0}", tableName.Replace(".", "_"));
			// Check for table length?
			return String.Format("{0}_stage", tableName.Replace(".", "_"));
		}

		SqlCEDataAccessor dataAccessor;
		String connectionString;
		private List<DbView.FieldInfo> Fields {get; set;}

		// Try to keep template variables constent.
		// {0} is target table {1} is staging {2} is a list of fields to update 
		// {3} is a pkey comparison {4} is an update statement
		const String SCRIPT_REPLACE = @"
delete from {0};
insert into {0} ({2}) 
     select {2} from {1};
";
		const String SCRIPT_MERGE = @"
insert into {0} ({2}) 
     select {2} from {1} 
	  where not exists (select * from {0} where {3});
";
		const String SCRIPT_UPDATE_ONLY = @"
";
		const String SCRIPT_APPEND_ONLY = @"
insert into {0} ({2}) 
     select {2} from {1} 
	  where not exists (select * from {0} where {3});
";

		const String UPDATE_SPECIAL = @"
update {0} set {2} = {1}.{2}
  from {1} where {3}
  and {1}.{2} is not null;
";
	}
}
